﻿-- =============================================
-- Author:		David Settino
-- Create date: 01/18/2011
-- Description:	Converts string or xml data type
--    to geography data type before insert
-- =============================================
CREATE TRIGGER [dbo].[ExposureCacheInsert] 
ON [dbo].[ExposureCache]
AFTER INSERT AS
BEGIN
  DECLARE @Circle VARCHAR(500)
  DECLARE @Envelope VARCHAR(100)
  DECLARE @Polygon XML
  DECLARE @GeoRSS XML
  DECLARE @GeoRSSWhere [geography]
  DECLARE @circleLocation varchar(100)
  DECLARE @index int
  DECLARE @id int
  DECLARE @Link VARCHAR(500)
  
  SET @Circle = (SELECT Circle FROM inserted)
  IF (@Circle IS NOT NULL)
    BEGIN
      SET @GeoRSSWhere = [dbo].[CircleToGeography](@Circle)
    END
  SET @GeoRSS = (SELECT GeoRSS FROM inserted)
  IF (@GeoRSS IS NOT NULL)
    BEGIN
      SET @GeoRSSWhere = geography::GeomFromGml(@GeoRSS ,4326) 
    END
  SET @Envelope = (SELECT Envelope FROM inserted)
  IF (@Envelope IS NOT NULL)
    BEGIN
      SET @GeoRSSWhere = geography::STPolyFromText(@Envelope, 4326)
    END
  SET @Polygon = (SELECT Polygon FROM inserted)    
  IF (@Polygon IS NOT NULL)
    BEGIN
      SET @GeoRSSWhere = geography::GeomFromGml(@Polygon ,4326)
    END
  SET @Link = (SELECT Link FROM inserted)
  SET @id = (SELECT ID  FROM inserted)
  SET @Link = REPLACE(@Link, '00000000-0000-0000-0000-000000000000', CAST(@Id AS VARCHAR))
  UPDATE [dbo].[ExposureCache]
  SET GeoRSSWhere = @GeoRSSWhere
	,Link = @Link
  WHERE id = @id
END
